{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dressmaker - Hard\n",
    "You may need to create views to complete these questions - but you do not have permission to create tables or views in the default schema. Your SQL commands are executed by user scott in schema gisq - you may create or drop views and tables in schema scott but not in gisq."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 60)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "jmcust = pd.read_sql_table('jmcust', engine)\n",
    "dressmaker = pd.read_sql_table('dressmaker', engine)\n",
    "dress_order = pd.read_sql_table('dress_order', engine)\n",
    "construction = pd.read_sql_table('construction', engine)\n",
    "quantities = pd.read_sql_table('quantities', engine)\n",
    "order_line = pd.read_sql_table('order_line', engine)\n",
    "garment = pd.read_sql_table('garment', engine)\n",
    "material = pd.read_sql_table('material', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "When creating a view in scott you must specify the schema name of the sources and the destination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "scott = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "It is decided to review the materials stock. How much did each material contribute to turnover in 2002?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>material_no</th>\n",
       "      <th>fabric</th>\n",
       "      <th>colour</th>\n",
       "      <th>pattern</th>\n",
       "      <th>quantity</th>\n",
       "      <th>cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Black</td>\n",
       "      <td>Plain</td>\n",
       "      <td>4.9</td>\n",
       "      <td>34.300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Red Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>9.3</td>\n",
       "      <td>93.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>5.7</td>\n",
       "      <td>17.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>2.2</td>\n",
       "      <td>6.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Black Dotted</td>\n",
       "      <td>Woven</td>\n",
       "      <td>6.4</td>\n",
       "      <td>19.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Red Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>2.2</td>\n",
       "      <td>6.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Pale Yellow</td>\n",
       "      <td>Printed</td>\n",
       "      <td>4.3</td>\n",
       "      <td>10.965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>4.2</td>\n",
       "      <td>12.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Pink Check</td>\n",
       "      <td>Woven</td>\n",
       "      <td>4.6</td>\n",
       "      <td>13.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>8.3</td>\n",
       "      <td>124.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Rayon</td>\n",
       "      <td>Red/Orange</td>\n",
       "      <td>Printed</td>\n",
       "      <td>9.7</td>\n",
       "      <td>38.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Serge</td>\n",
       "      <td>Navy Blue</td>\n",
       "      <td>Woven</td>\n",
       "      <td>4.5</td>\n",
       "      <td>24.750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>4.6</td>\n",
       "      <td>16.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>4.5</td>\n",
       "      <td>15.750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    material_no                fabric                colour  \\\n",
       "0             1  Silk                  Black                  \n",
       "1             2  Silk                  Red Abstract           \n",
       "2             3  Cotton                Yellow Stripe          \n",
       "3             4  Cotton                Green Stripe           \n",
       "4             5  Cotton                Black Dotted           \n",
       "5             6  Cotton                Red Stripe             \n",
       "6             7  Polyester             Pale Yellow            \n",
       "7             8  Cotton                Blue Stripe            \n",
       "8             9  Cotton                Pink Check             \n",
       "9            10  Silk                  Green Abstract         \n",
       "10           11  Rayon                 Red/Orange             \n",
       "11           12  Serge                 Navy Blue              \n",
       "12           13  Cotton                Blue Abstract          \n",
       "13           14  Cotton                Green Abstract         \n",
       "\n",
       "                 pattern  quantity     cost  \n",
       "0   Plain                      4.9   34.300  \n",
       "1   Printed                    9.3   93.000  \n",
       "2   Woven                      5.7   17.100  \n",
       "3   Woven                      2.2    6.600  \n",
       "4   Woven                      6.4   19.200  \n",
       "5   Woven                      2.2    6.600  \n",
       "6   Printed                    4.3   10.965  \n",
       "7   Woven                      4.2   12.600  \n",
       "8   Woven                      4.6   13.800  \n",
       "9   Printed                    8.3  124.500  \n",
       "10  Printed                    9.7   38.800  \n",
       "11  Woven                      4.5   24.750  \n",
       "12  Printed                    4.6   16.100  \n",
       "13  Printed                    4.5   15.750  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (material.merge(order_line, left_on='material_no', right_on='ol_material')\n",
    "     .merge(quantities, left_on=['ol_size', 'ol_style'], \n",
    "            right_on=['size_q', 'style_q'])\n",
    "     .merge(dress_order.loc[dress_order['order_date'].dt.year==2002],\n",
    "            left_on='order_ref', right_on='order_no'))\n",
    "t['cost'] = t['cost'] * t['quantity']\n",
    "(t.groupby(['material_no', 'fabric', 'colour', 'pattern'])\n",
    " .agg({'quantity': 'sum', 'cost': 'sum'}).reset_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "An order for shorts has just been placed and the work is to be distributed amongst the workforce, and we wish to know how busy the shorts makers are. For each of the workers who have experience of making shorts show the number of hours work that she is currently committed to, assuming a meagre wage of £4.50 per hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>d_name</th>\n",
       "      <th>hrs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Miss Pins</td>\n",
       "      <td>28.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>49.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mr Needles</td>\n",
       "      <td>18.611111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Mr Seam</td>\n",
       "      <td>28.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mr Taylor</td>\n",
       "      <td>18.611111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ms Sew</td>\n",
       "      <td>18.611111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 d_name        hrs\n",
       "0  Miss Pins             28.166667\n",
       "1  Miss Stitch           49.166667\n",
       "2  Mr Needles            18.611111\n",
       "3  Mr Seam               28.166667\n",
       "4  Mr Taylor             18.611111\n",
       "5  Ms Sew                18.611111"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shorts = (dressmaker.merge(construction, left_on='d_no', right_on='maker')\n",
    "          .merge(dress_order, left_on='order_ref', right_on='order_no')\n",
    "          .merge(order_line, left_on='order_no', right_on='order_ref')\n",
    "          .merge(garment.loc[garment['description'].str.strip().str.lower()=='shorts'], \n",
    "                 left_on='ol_style', right_on='style_no')\n",
    "          ['d_no'].drop_duplicates())\n",
    "(dressmaker.loc[dressmaker['d_no'].isin(shorts)]\n",
    "  .merge(construction, left_on='d_no', right_on='maker')\n",
    "  .merge(dress_order.loc[dress_order['completed']=='N'], \n",
    "         left_on='order_ref', right_on='order_no')\n",
    "  .merge(order_line, left_on='order_no', right_on='order_ref')\n",
    "  .merge(garment.assign(hrs=garment['labour_cost']/4.5), \n",
    "         left_on='ol_style', right_on='style_no')\n",
    "  .merge(quantities, left_on=['ol_size', 'ol_style'],\n",
    "         right_on=['size_q', 'style_q'])\n",
    "  .groupby('d_name')['hrs'].sum()\n",
    "  .reset_index())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "\"Big spender of the year\" is the customer who spends the most on high value items. Identify the \"Big spender of the year 2002\" if the \"high value\" threshold is set at £30. Also who would it be if the threshold was £20 or £50?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>c_name</th>\n",
       "      <th>tot_cost</th>\n",
       "      <th>thres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>198.54</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ms White</td>\n",
       "      <td>173.55</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>72.00</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 c_name  tot_cost  thres\n",
       "1  Mr Brass                198.54     20\n",
       "4  Ms White                173.55     30\n",
       "0  Mr Brass                 72.00     50"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (order_line.merge(quantities, left_on=['ol_style', 'ol_size'], \n",
    "                      right_on=['style_q', 'size_q'])\n",
    "     .merge(garment, left_on='ol_style', right_on='style_no')\n",
    "     .merge(material, left_on='ol_material', right_on='material_no'))\n",
    "t['tot_cost'] = t['labour_cost'] + t['quantity'] * t['cost']\n",
    "\n",
    "c = (dress_order.loc[dress_order['order_date'].dt.year==2002]\n",
    "     .merge(jmcust, left_on='cust_no', right_on='c_no')\n",
    "     .merge(t[['order_ref', 'line_no', 'tot_cost']], \n",
    "            left_on='order_no', right_on='order_ref'))\n",
    "\n",
    "def find_big_spender(thres: float):\n",
    "    ret = (c.loc[c['tot_cost']>=thres].groupby('c_name')['tot_cost'].sum()\n",
    "           .reset_index().sort_values('tot_cost', ascending=False).iloc[:1])\n",
    "    return ret[['c_name', 'tot_cost']].assign(thres=thres)\n",
    "\n",
    "pd.concat(map(find_big_spender, [20, 30, 50]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Who is the fastest at making trousers?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>d_no</th>\n",
       "      <th>d_name</th>\n",
       "      <th>days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Mr Needles</td>\n",
       "      <td>3 days</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   d_no                d_name   days\n",
       "3     3  Mr Needles           3 days"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (dressmaker.merge(construction, left_on='d_no', right_on='maker')\n",
    "     .merge(order_line, left_on=['order_ref', 'line_ref'],\n",
    "            right_on=['order_ref', 'line_no'])\n",
    "     .merge(garment.loc[garment['description'].str.strip().str.lower()=='trousers'], \n",
    "            left_on='ol_style', right_on='style_no'))\n",
    "t['days'] = t['finish_date'] - t['start_date']\n",
    "t[['d_no', 'd_name', 'days']].sort_values('days').iloc[:1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "\"Employee of the month\" is the seamstress who completes the greatest value of clothes. Show the \"employees of the month\" for months in 2002."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>d_name</th>\n",
       "      <th>month</th>\n",
       "      <th>val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>1</td>\n",
       "      <td>49.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Mrs Hem</td>\n",
       "      <td>2</td>\n",
       "      <td>122.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>3</td>\n",
       "      <td>97.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  d_name  month     val\n",
       "2   Miss Stitch               1   49.00\n",
       "14  Mrs Hem                   2  122.25\n",
       "4   Miss Stitch               3   97.20"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = (material.merge(order_line, left_on='material_no', right_on='ol_material')\n",
    "     .merge(quantities, left_on=['ol_size', 'ol_style'], \n",
    "            right_on=['size_q', 'style_q'])\n",
    "     .merge(garment, left_on='ol_style', right_on='style_no')\n",
    "     .merge(construction.loc[construction['start_date'].dt.year==2002], \n",
    "            left_on=['order_ref', 'line_no'],\n",
    "            right_on=['order_ref', 'line_ref'])\n",
    "     .merge(dressmaker, left_on='maker', right_on='d_no'))\n",
    "t['month'] = t['start_date'].dt.month\n",
    "t['val'] = t['quantity'] * t['cost'] + t['labour_cost']\n",
    "(t.groupby(['d_name', 'month'])['val'].sum().reset_index()\n",
    " .sort_values('val').groupby('month').tail(1)\n",
    " .sort_values('month'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
